package com.mentaleye.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.mentaleye.dal.model.Students;

public class StudentsUtil {
	/**
	 * 读取excel中的数据
	 * 
	 * @param path
	 * @return List<StudentBean>
	 * @author zhang 2015-08-18 00:08
	 */
	public static List<Students> readExcel(String path) {

		if (path != null && !path.equals("")) {
			String ext = getExt(path);
			if (ext != null && !ext.equals("")) {
				if (ext.equals("xls")) {
					return readXls(path);
				} else if (ext.equals("xlsx")) {
					return readXlsx(path);
				}
			}
		}
		return new ArrayList<Students>();
	}

	/**
	 * 读取后缀为xls的excel文件的数据
	 * 
	 * @param path
	 * @return List<StudentBean>
	 * @author zhang 2015-08-18 00:10
	 */
	private static List<Students> readXls(String path) {

		HSSFWorkbook hssfWorkbook = null;
		try {
			InputStream is = new FileInputStream(path);
			hssfWorkbook = new HSSFWorkbook(is);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		Students students = null;
		List<Students> list = new ArrayList<Students>();
		if (hssfWorkbook != null) {
			// Read the Sheet
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}
				// Read the Row
				for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					HSSFRow hssfRow = hssfSheet.getRow(rowNum);
					if (hssfRow != null) {
						students = new Students();
						students.setId(getValueInteger(hssfRow.getCell(0)));
						students.setStuId(getValue(hssfRow.getCell(1)));
						students.setStuName(getValue(hssfRow.getCell(2)));
						students.setSex(getValueInteger(hssfRow.getCell(3)));
						students.setCollege(getValue(hssfRow.getCell(4)));
						students.setMajor(getValue(hssfRow.getCell(5)));
						students.setGraduationDate(DateFormatter.parse(getValue(hssfRow.getCell(6)), "yyyy-mm-dd"));
						students.setEducation(getValueInteger(hssfRow.getCell(7)));
						students.setStuTel(getValue(hssfRow.getCell(8)));
						students.setParentTel(getValue(hssfRow.getCell(9)));
						students.setQq(getValue(hssfRow.getCell(10)));
						students.setEmail(getValue(hssfRow.getCell(11)));
						students.setIdentityId(getValue(hssfRow.getCell(12)));
						students.setCardId(getValue(hssfRow.getCell(13)));
						students.setAddress(getValue(hssfRow.getCell(14)));
						students.setStuType(getValue(hssfRow.getCell(15)));
						students.setState(getValueInteger(hssfRow.getCell(16)));
						students.setRemark(getValue(hssfRow.getCell(17)));
						list.add(students);
					}
				}
			}
		}
		return list;
	}

	/**
	 * 读取后缀为xlsx的excel文件的数据
	 * 
	 * @param path
	 * @return List<StudentBean>
	 * @author zhang 2015-08-18 00:08
	 */
	private static List<Students> readXlsx(String path) {

		XSSFWorkbook xssfWorkbook = null;
		try {
			InputStream is = new FileInputStream(path);
			xssfWorkbook = new XSSFWorkbook(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		Students students = null;
		List<Students> list = new ArrayList<Students>();
		if (xssfWorkbook != null) {
			// Read the Sheet
			for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
				XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
				if (xssfSheet == null) {
					continue;
				}
				// Read the Row
				for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					XSSFRow xssfRow = xssfSheet.getRow(rowNum);
					if (xssfRow != null) {
						students = new Students();
						students.setId(getValueInteger(xssfRow.getCell(0)));
						students.setStuId(getValue(xssfRow.getCell(1)));
						students.setStuName(getValue(xssfRow.getCell(2)));
						students.setSex(getValueInteger(xssfRow.getCell(3)));
						students.setCollege(getValue(xssfRow.getCell(4)));
						students.setMajor(getValue(xssfRow.getCell(5)));
						students.setGraduationDate(DateFormatter.parse(getValue(xssfRow.getCell(6)), "yyyy-mm-dd"));
						students.setEducation(getValueInteger(xssfRow.getCell(7)));
						students.setStuTel(getValue(xssfRow.getCell(8)));
						students.setParentTel(getValue(xssfRow.getCell(9)));
						students.setQq(getValue(xssfRow.getCell(10)));
						students.setEmail(getValue(xssfRow.getCell(11)));
						students.setIdentityId(getValue(xssfRow.getCell(12)));
						students.setCardId(getValue(xssfRow.getCell(13)));
						students.setAddress(getValue(xssfRow.getCell(14)));
						students.setStuType(getValue(xssfRow.getCell(15)));
						students.setState(getValueInteger(xssfRow.getCell(16)));
						students.setRemark(getValue(xssfRow.getCell(17)));
						list.add(students);
					}
				}
			}
		}
		return list;
	}

	/**
	 * 获取文件扩展名
	 * 
	 * @param path
	 * @return String
	 * @author zhang 2015-08-17 23:26
	 */
	private static String getExt(String path) {
		if (path == null || path.equals("") || !path.contains(".")) {
			return null;
		} else {
			return path.substring(path.lastIndexOf(".") + 1, path.length());
		}
	}

	/**
	 * 判断后缀为xlsx的excel文件的数据类型
	 * 
	 * @param xssfRow
	 * @return String
	 * @author zhang 2015-08-18 00:12
	 */
	@SuppressWarnings({ "static-access", "deprecation" })
	private static String getValue(XSSFCell xssfRow) {
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			DecimalFormat df = new DecimalFormat("0");
			return String.valueOf(df.format(xssfRow.getNumericCellValue()));
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}

	@SuppressWarnings({ "deprecation", "static-access" })
	private static Integer getValueInteger(XSSFCell xssfCell) {
		if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
			DecimalFormat df = new DecimalFormat("0");
			return Integer.valueOf(df.format((int) xssfCell.getNumericCellValue()));
		} else {
			return Integer.valueOf(xssfCell.getStringCellValue());
		}
	}

	/**
	 * 判断后缀为xls的excel文件的数据类型
	 * 
	 * @param hssfCell
	 * @return String
	 * @author zhang 2015-08-18 00:12
	 */
	@SuppressWarnings({ "static-access", "deprecation" })
	private static String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			DecimalFormat df = new DecimalFormat("0");
			return String.valueOf(df.format(hssfCell.getNumericCellValue()));
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

	@SuppressWarnings({ "static-access", "deprecation" })
	private static Integer getValueInteger(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			DecimalFormat df = new DecimalFormat("0");
			return Integer.valueOf(df.format((int) hssfCell.getNumericCellValue()));
		} else {
			return Integer.valueOf(hssfCell.getStringCellValue());
		}
	}

}
